<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class Rank extends Command
{

    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'rank';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '排行';

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {

        DB::table('firm_brands')
            ->update([
                'last_month_like_rank' => 0,
                'last_month_form_rank' => 0,
                'last_month_view_rank' => 0,
            ]);

        DB::table('articles')
            ->update([
                'last_month_book_rank' => 0,
                'last_month_new_rank' => 0,
            ]);

        DB::table('circles')
            ->update([
                'last_month_comment_rank' => 0,
            ]);

        DB::table('courses')
            ->update([
                'last_month_view_rank' => 0,
            ]);

        DB::table('firms')
            ->update([
                'last_month_view_rank' => 0,
                'last_month_form_rank' => 0,
            ]);

        list($year,$month) = explode('-',date('Y-m',strtotime('-1 month')));
        $brandLike =  DB::table('user_like_brands')
            ->selectRaw('brand_id,count(brand_id) as cur_month_rank')
            ->whereYear('like_time',$year)
            ->whereMonth('like_time',$month)
            ->where('status',1)
            ->groupBy('brand_id')
            ->orderByDesc('cur_month_rank')
            ->get();

        if ($brandLike->isNotEmpty()){
            foreach ($brandLike as $k => $like){
                DB::table('firm_brands')
                    ->where('id',$like->brand_id)
                    ->update([
                       'last_month_like_rank' => $k+1
                    ]);
            }
        }


        $firmForm =  DB::table('firm_messages')
            ->join('firms','firm_messages.firm_id','=','firms.id')
            ->selectRaw('firm_messages.firm_id,count(firm_messages.firm_id) as r')
            ->whereYear('firm_messages.created_at',$year)
            ->whereMonth('firm_messages.created_at',$month)
            ->groupBy('firm_messages.firm_id')
            ->where('firms.category_id',2)
            ->orderByDesc('r')
            ->get();


        if ($firmForm->isNotEmpty()){
            foreach ($firmForm as $k => $form){
                DB::table('firms')
                    ->where('id',$form->firm_id)
                    ->update([
                        'last_month_form_rank' => $k+1
                    ]);
            }
        }


        $firmViews = DB::table('firm_browses')
            ->join('firms','firm_browses.firm_id','=','firms.id')
            ->where('firms.category_id',2)
            ->selectRaw('firm_browses.firm_id,count(firm_browses.firm_id) as r')
            ->whereYear('firm_browses.view_time',$year)
            ->whereMonth('firm_browses.view_time',$month)
            ->groupBy('firm_browses.firm_id')
            ->orderByDesc('r')
            ->get();


        if ($firmViews->isNotEmpty()){
            foreach ($firmViews as $k => $view){
                DB::table('firms')
                    ->where('id',$view->firm_id)
                    ->update([
                        'last_month_view_rank' => $k+1
                    ]);
            }
        }


        $courseViews = DB::table('course_browses')
            ->join('courses','course_browses.course_id','=','courses.id')
            ->selectRaw('course_browses.course_id,count(course_browses.course_id) as r')
            ->whereYear('course_browses.view_time',$year)
            ->whereMonth('course_browses.view_time',$month)
            ->groupBy('course_browses.course_id')
            ->orderByDesc('r')
            ->get();



        if ($courseViews->isNotEmpty()){
            foreach ($courseViews as $k => $view){
                DB::table('courses')
                    ->where('id',$view->course_id)
                    ->update([
                        'last_month_view_rank' => $k+1
                    ]);
            }
        }



        $bookView = DB::table('user_collect_articles')
            ->join('articles','user_collect_articles.article_id','=','articles.id')
            ->selectRaw('user_collect_articles.article_id,count(user_collect_articles.article_id) as r')
            ->whereYear('user_collect_articles.collect_time',$year)
            ->whereMonth('user_collect_articles.collect_time',$month)
            ->groupBy('user_collect_articles.article_id')
            ->where('articles.category_id',17)
            ->orderByDesc('r')
            ->get();


        if ($bookView->isNotEmpty()){
            foreach ($bookView as $k => $view){
                DB::table('articles')
                    ->where('id',$view->article_id)
                    ->update([
                        'last_month_book_rank' => $k+1
                    ]);
            }
        }



        $circleComment = DB::table('article_comments')
            ->join('articles','article_comments.article_id','=','articles.id')
            ->selectRaw('articles.circle_id,count(articles.circle_id) as r')
            ->whereYear('article_comments.created_at',$year)
            ->whereMonth('article_comments.created_at',$month)
            ->groupBy('articles.circle_id')
            ->where('articles.circle_id','>',0)
            ->orderByDesc('r')
            ->get();



        if ($circleComment->isNotEmpty()){
            foreach ($circleComment as $k => $c){
                DB::table('circles')
                    ->where('id',$c->circle_id)
                    ->update([
                        'last_month_comment_rank' => $k+1
                    ]);
            }
        }




        $newView = DB::table('article_browses')
            ->join('articles','article_browses.article_id','=','articles.id')
            ->selectRaw('article_browses.article_id,count(article_browses.article_id) as r')
            ->whereYear('article_browses.view_time',$year)
            ->whereMonth('article_browses.view_time',$month)
            ->groupBy('article_browses.article_id')
            ->where('articles.type',4)
            ->orderByDesc('r')
            ->get();


        if ($newView->isNotEmpty()){
            foreach ($newView as $k => $c){
                DB::table('articles')
                    ->where('id',$c->article_id)
                    ->update([
                        'last_month_new_rank' => $k+1
                    ]);
            }
        }



        $brandForm = DB::table('firm_brand_messages')
            ->join('firm_brands','firm_brand_messages.brand_id','=','firm_brands.id')
            ->selectRaw('firm_brand_messages.brand_id,count(firm_brand_messages.brand_id) as r')
            ->whereYear('firm_brand_messages.created_at',$year)
            ->whereMonth('firm_brand_messages.created_at',$month)
            ->groupBy('firm_brand_messages.brand_id')
            ->orderByDesc('r')
            ->get();


        if ($brandForm->isNotEmpty()){
            foreach ($brandForm as $k => $c){
                DB::table('firm_brands')
                    ->where('id',$c->brand_id)
                    ->update([
                        'last_month_form_rank' => $k+1
                    ]);
            }
        }


        $brandView = DB::table('firm_brand_browses')
            ->join('firm_brands','firm_brand_browses.brand_id','=','firm_brands.id')
            ->selectRaw('firm_brand_browses.brand_id,count(firm_brand_browses.brand_id) as r')
            ->whereYear('firm_brand_browses.view_time',$year)
            ->whereMonth('firm_brand_browses.view_time',$month)
            ->groupBy('firm_brand_browses.brand_id')
            ->orderByDesc('r')
            ->get();


        if ($brandView->isNotEmpty()){
            foreach ($brandView as $k => $c){
                DB::table('firm_brands')
                    ->where('id',$c->brand_id)
                    ->update([
                        'last_month_view_rank' => $k+1
                    ]);
            }
        }

    }

}
